package com.EWS.Dao.impl;

import com.EWS.Dao.UserDao;
import com.EWS.model.UserDto;
import com.EWS.service.UserService;
import com.EWS.service.impl.UserServiceImpl;
import com.EWS.util.DbUtil;
import com.EWS.util.MD5Utils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

public class UserDaoImpl extends DbUtil<UserDto> implements UserDao {
    @Override
    // 注册：Dao层与数据库交互
    public int register(UserDto userDto) {
        String sql = " insert into users (username,password,salt) value (?,?,?)";
        return DbUtil.prePareStmetExcute(sql,new Object[]{userDto.getUserName(),userDto.getPassWord(),userDto.getSalt()});
    }

    @Override
    // 登录：Dao层与数据库交互，返回该用户的信息到Service层
    public UserDto login(String userName) {
        // 在数据库中查询该用户
        String sql = " select * from users where username = ? ";
        // 在进行该操作时用户表中的信息已经转化为javaBean实体类，因为getDataList()方法中调用了getEntity()方法
        List<UserDto> userDtoList = super.getDataList(sql,new Object[]{userName});
        return userDtoList != null && userDtoList.size()>0 ? userDtoList.get(0) : null;
    }

    @Override
    public List<UserDto> getUserStudy(String userName,String word) {
        // 如果word不为空，说明是在单词库页面进行模糊查询

        // 在数据库中查询该用户的学习记录
        // 动态拼接sql
        List<String> paraList = new ArrayList<>();
        String sql = " select s.id,u.username,u.password,u.salt,w.word,w.definition,s.count,s.time from users u,words w,study s where u.username = s.username and u.username = w.username and w.word = s.word ";
        if(userName != null && !"".equals(userName)){
            sql += " and u.username = ? ";
            paraList.add(userName);
        }
        // 在单词库页面查询时使用，在其他页面该值永远为空
        if(word != null && !"".equals(word)){
            sql += " and w.word like ? ";
            paraList.add(word);
        }
        // 对学习记录进行排序
        sql += " order by case when time = '未学' then 0 else 1 end, time asc,count asc ";
                
        Object[] obj = new Object[paraList.size()];
        int index = 0;
        if(userName != null && !"".equals(userName)){
            obj[index++] = userName;
        }
        if(word != null && !"".equals(word)){
            obj[index++] = "%"+word+"%";
        }
        List<UserDto> userDtoList = super.getDataList(sql,obj);
        return userDtoList!=null&&userDtoList.size()>0?userDtoList:null;
    }

    @Override
    public int addWord(String userName,String word, String definition) {
        // 在单词库中添加单词
        String sqlW = " insert into words (username,word,definition) value (?,?,?)";
        // 同时在学习记录中为该用户添加上该单词，标记为学习次数0、学习时间未学
        String sqlS = " insert into study (username,word) value(?,?) ";
        return DbUtil.prePareStmetExcute(sqlW,new Object[]{userName,word,definition}) + DbUtil.prePareStmetExcute(sqlS,new Object[]{userName,word});
    }

    @Override
    public int updateStudy(String userName, String word) {
        // 修改学习记录中该用户、改单词的学习次数、学习时间
        String sql = " update study set count = count + 1,time = DATE_FORMAT(NOW(),'%Y-%m-%d') where username = ? and word = ? ";
        return DbUtil.prePareStmetExcute(sql,new Object[]{userName,word});
    }

    @Override
    public int deleteWord(String userName, String word) {
        // 在该用户的单词库、学习记录中删除该单词
        String sqlW = " delete from words where word = ? ";
        String sqlS = " delete from study where username = ? and word = ? ";
        return DbUtil.prePareStmetExcute(sqlW,new Object[]{word}) + DbUtil.prePareStmetExcute(sqlS,new Object[]{userName,word});
    }

    @Override
    public int updatePassword(UserDto userDto, String password) {
        // 修改数据库中用户表中该用户的密码
        // 将新密码用原来的盐值加密后上传数据库
        userDto.setPassWord(MD5Utils.stringToMD5(password+userDto.getSalt()));
        String sql = " update users set password = ? where username = ? ";
        return DbUtil.prePareStmetExcute(sql,new Object[]{userDto.getPassWord(),userDto.getUserName()});
    }

    @Override
    public UserDto getUserByName(String userName) {
        String sql = " select *  from  users where username = ? ";
        List<UserDto> userDtoList = super.getDataList(sql,new Object[]{userName});
        return userDtoList !=null && userDtoList.size()>0?userDtoList.get(0):null;
    }

    @Override
    public UserDto getEntity(ResultSet rs) {
        UserDto userDto = new UserDto();
        try{
            userDto.setId(rs.getInt("id"));
            userDto.setUserName(rs.getString("username"));
            userDto.setPassWord(rs.getString("password"));
            userDto.setSalt(rs.getString("salt"));
            userDto.setWord(rs.getString("word"));
            userDto.setDefinition(rs.getString("definition"));
            userDto.setCount(rs.getInt("count"));
            userDto.setTime(rs.getString("time"));
        }catch(SQLException ce){
            ce.printStackTrace();
        }
        return userDto;
    }
}
